/*
 * Class: DBMEvent
 * 
 * Description: Implements all the statements to interact with the events
 * 				stored in the database
 * 
 * Version 1.0 M-A Lavoie 2011-11-11
 * 
 * Version 1.1 Mohamed Ali Ayed - 2011-11-15
 *
 * Version 1.2 Mohamed Ali Ayed - 2011-11-16
 * 
 * Version 1.3 M-A Lavoie - 2011-12-04
 * 		Renamed getters and setters to lowerCase.
 * 		Changed events list from HashSet to ArrayList.
 * 		Added missing parameters to constructor.
 * 
 * Version 1.4 Sonny Guenette - 2011-12-08
 * 		Changed variables and parameters names to respect the team naming convention
 * 
 * Version 1.5 Sonny Guenette - 2011-12-09
 * 		Added new method "checkConflictEvent"
 * 
 * Copyright GPP - Genuine People Personality inc.
 * 
 */

package dbManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import calendarEventManager.CEMEvent;

public class DBMEvent {
	private PreparedStatement m_oPst;
	private ResultSet m_oRset;

	//**************************************************************
	// Method: createEvent
	// Description: Add a new event in the database
	//**************************************************************
	/**
	 * @param p_oEvent The event to add
	 * @param p_oCon The connection object
	 * 
	 * @return true if the event is successfully added
	 */
	public boolean createEvent(CEMEvent p_oEvent, Connection p_oCon) {
		try {
			//insert into events DataBase
			m_oPst = p_oCon.prepareStatement("INSERT INTO events (name, startTime, endTime, description, agenda_id) VALUES (?,?,?,?,?)");
			m_oPst.setString(1, p_oEvent.getName());
			m_oPst.setTimestamp(2, p_oEvent.getStartingTime());
			m_oPst.setTimestamp(3, p_oEvent.getEndingTime());
			m_oPst.setString(4, p_oEvent.getDescription());
			m_oPst.setInt(5, p_oEvent.getAgendaId());
			m_oPst.executeUpdate();

		} catch (SQLException ex) {
			Logger lgr = Logger.getLogger(DBMEvent.class.getName());
			lgr.log(Level.SEVERE, ex.getMessage(), ex);
			return false;
		} // end catch 
		return true;
	}	//end createEvent

	//**************************************************************
	// Method: updateEvent
	// Description: Modify an event
	//**************************************************************
	/**
	 * @param p_oEvent The new event
	 * @param p_oCon The connection object
	 * 
	 * @return true if the event is successfully modified
	 */
	public boolean updateEvent(CEMEvent p_oEvent, Connection p_oCon){
		try {       
			m_oPst = p_oCon.prepareStatement("UPDATE events SET name=?, startTime=?, endTime=?, description=?, agenda_id=? WHERE event_id=?");
			m_oPst.setString(1, p_oEvent.getName());
			m_oPst.setTimestamp(2, p_oEvent.getStartingTime());
			m_oPst.setTimestamp(3, p_oEvent.getEndingTime());
			m_oPst.setString(4, p_oEvent.getDescription());
			m_oPst.setInt(5, p_oEvent.getAgendaId());
			m_oPst.setInt(6, p_oEvent.getEventId());
			m_oPst.executeUpdate();
		} catch (SQLException ex) {
			Logger lgr = Logger.getLogger(DBMEvent.class.getName());
			lgr.log(Level.SEVERE, ex.getMessage(), ex);
			return false;
		}// end catch 
		return true;	
	}//end updateEvent

	//**************************************************************
	// Method: deleteEvent
	// Description: delete an event
	//**************************************************************
	/**
	 * @param p_nEventId The event id
	 * @param p_oCon The connection object
	 * 
	 * @return true if the event is successfully deleted
	 */
	public boolean deleteEvent(int p_nEventId,  Connection p_oCon){
		try {       
			m_oPst = p_oCon.prepareStatement("DELETE FROM events WHERE event_id= ?");
			m_oPst.setInt(1,p_nEventId);
			m_oPst.executeUpdate();
		} catch (SQLException ex) {
			Logger lgr = Logger.getLogger(DBMEvent.class.getName());
			lgr.log(Level.SEVERE, ex.getMessage(), ex);
			return false;
		}// end catch 
		return true;	    	
	}// end deleteEvent

	//**************************************************************
	// Method: selectAllTupleEvent
	// Description: select all the event for an agenda
	//**************************************************************
	/**
	 * @param p_oCon The connection object
	 * @param p_oEventList The list that contain all the events
	 * @param p_nAgendaId The agenda id from which to retrieve the events
	 * 
	 * @return true if the events are successfully retrieved
	 */
	public boolean selectAllTupleEvent(Connection p_oCon, List<CEMEvent> p_oEventList, int p_nAgendaId){
		try {       
			m_oPst = p_oCon.prepareStatement("SELECT * FROM events WHERE agenda_id=?");
			m_oPst.setInt(1, p_nAgendaId);
			m_oRset = m_oPst.executeQuery();
			while (m_oRset.next()) {
				CEMEvent oCEMEvent = new CEMEvent(m_oRset.getInt(1), m_oRset.getString(2), m_oRset.getTimestamp(3),
						m_oRset.getTimestamp(4), m_oRset.getString(5), m_oRset.getInt(6));
				p_oEventList.add(oCEMEvent);
			}
		} catch (SQLException ex) {
			Logger lgr = Logger.getLogger(DBMEvent.class.getName());
			lgr.log(Level.SEVERE, ex.getMessage(), ex);
			return false;
		}// end catch 
		return true;	
	}// end selectAllTupleEvent

	//**************************************************************
	// Method: getEventId
	// Description: Retrieve the id for an event
	//**************************************************************
	/**
	 * @param p_oNewEvent The event from which we want the id
	 * @param p_oCon The connection object
	 * 
	 * @return the event id or -1 if there is an error
	 */
	public int getEventId(CEMEvent p_oNewEvent, Connection p_oCon){
		try{
			m_oPst = p_oCon.prepareStatement("SELECT event_id FROM events WHERE startTime=? AND agenda_id=?");
			m_oPst.setTimestamp(1,p_oNewEvent.getStartingTime());
			m_oPst.setInt(2, p_oNewEvent.getAgendaId());
			m_oRset = m_oPst.executeQuery();
			m_oRset.next();
			return m_oRset.getInt(1);
		} catch (SQLException ex) {
			return -1;
		}// end catch
	}// end getEventId()

	//**************************************************************
	// Method: checkConflictEvent
	// Description: Check if there is a schedule conflict before
	//				adding the new event.
	//**************************************************************
	/**
	 * @param p_onewEvent The event to add
	 * @param p_oCon The connection object
	 * 
	 * @return true if there is a conflict
	 */
	public boolean checkConflictEvent(CEMEvent p_oNewEvent, Connection p_oCon){
		try{
			m_oPst = p_oCon.prepareStatement("SELECT * FROM events " +
					"WHERE startTime BETWEEN ? AND ? " +
					"OR endTime BETWEEN ? AND ? " +
					"OR ? < startTime AND ? > endTime;");
			m_oPst.setTimestamp(1, p_oNewEvent.getStartingTime());
			m_oPst.setTimestamp(2, p_oNewEvent.getEndingTime());
			m_oPst.setTimestamp(3, p_oNewEvent.getStartingTime());
			m_oPst.setTimestamp(4, p_oNewEvent.getEndingTime());
			m_oPst.setTimestamp(5, p_oNewEvent.getStartingTime());
			m_oPst.setTimestamp(6, p_oNewEvent.getEndingTime());
			m_oRset = m_oPst.executeQuery();
			if (m_oRset.next()) {
				return true;
			}

		} catch (SQLException ex) {
			return true;
		}

		return false;
	}//end checkConflictEvent()

} // end class DBMEvent